using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.DataAccessProvider;
using DTN.Core;
using DTN.Core.Common;
namespace com.dtn.deals
{
	public class tblGroupFilterManager : ItblGroupFilterManager
	{
		private static ItblGroupFilterManager itblGroupFilterManager = null;
		private tblGroupFilterManager()
		{
		}
		public static ItblGroupFilterManager CreateInstance()
		{
			if (itblGroupFilterManager == null)
			{
				itblGroupFilterManager = new tblGroupFilterManager();
			}
			return itblGroupFilterManager;
		}
		public int InserttblGroupFilter(tblGroupFilter tblgroupfilter)
		{
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			int inumOfRowEffect = 0;
			SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@GroupFilterId", SqlDbType.Int),
																new SqlParameter("@GroupName", SqlDbType.NVarChar),
																new SqlParameter("@CategoryId", SqlDbType.Int),
                                                                new SqlParameter("@ParentCate", SqlDbType.Int),
                                                                new SqlParameter("@ParentGroup", SqlDbType.Int),
                                                                new SqlParameter("@desc", SqlDbType.NVarChar,500),
															};
			parameters[0].Direction = ParameterDirection.Output;
			parameters[1].Value = tblgroupfilter.GroupName;
			parameters[2].Value = tblgroupfilter.CategoryId;
            parameters[3].Value = tblgroupfilter.ParentCate;
            parameters[4].Value = tblgroupfilter.ParentId;
            parameters[5].Value = tblgroupfilter.Description;
			try
			{
				inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_InserttblGroupFilter", CommandType.StoredProcedure, parameters);
				tblgroupfilter.GroupFilterId = Convert.ToInt32(parameters[0].Value);
			}
			catch (Exception ex)
			{
				tblgroupfilter.GroupFilterId = -1;
			}
			return tblgroupfilter.GroupFilterId;
		}
		public bool UpdatetblGroupFilter(tblGroupFilter tblgroupfilter)
		{
			bool bresult = false;
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			int inumOfRowEffect = 0;
			SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@GroupFilterId", SqlDbType.Int),
																new SqlParameter("@GroupName", SqlDbType.NVarChar),
																new SqlParameter("@CategoryId", SqlDbType.Int),
                                                                new SqlParameter("@ParentCate", SqlDbType.Int),
                                                                new SqlParameter("@ParentGroup", SqlDbType.Int),
                                                                new SqlParameter("@desc", SqlDbType.NVarChar,500),
															};
			parameters[0].Value = tblgroupfilter.GroupFilterId;
			parameters[1].Value = tblgroupfilter.GroupName;
			parameters[2].Value = tblgroupfilter.CategoryId;
            parameters[3].Value = tblgroupfilter.ParentCate;
            parameters[4].Value = tblgroupfilter.ParentId;
            parameters[5].Value = tblgroupfilter.Description;
			try
			{
				inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_UpdatetblGroupFilter", CommandType.StoredProcedure, parameters);
				bresult = true;
			}
			catch (Exception ex)
			{
				bresult = false;
			}
			return bresult;
		}
		public bool DeletetblGroupFilter(int id)
		{
			bool bresult = false;
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			int inumOfRowEffect = 0;
			SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@GroupFilterId", SqlDbType.Int),
															};
			parameters[0].Value = id;
			try
			{
				inumOfRowEffect = dataProvider.ExecuteNonQuery("sp_DeletetblGroupFilter", CommandType.StoredProcedure, parameters);
				bresult = true;
			}
			catch (Exception ex)
			{
				bresult = false;
			}
			return bresult;
		}
		public tblGroupFilter SelecttblGroupFilterByID(int id)
		{
			IDataReader dr;
			tblGroupFilter tblgroupfilter = new tblGroupFilter();
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@GroupFilterId", SqlDbType.Int),
															};
			parameters[0].Value = id;
			try
			{
				dr = dataProvider.ExecuteReader("sp_SelecttblGroupFilter", CommandType.StoredProcedure, parameters);
				while(dr.Read())
				{
					tblgroupfilter.GroupFilterId = Convert.ToInt32(dr["GroupFilterId"].ToString());
					tblgroupfilter.GroupName = (dr["GroupName"].ToString());
                    tblgroupfilter.CategoryId = Convert.ToInt32(dr["CategoryId"].ToString());
                    tblgroupfilter.ParentCate =Convert.ToInt32(dr["ParentCategory"].ToString());
                    tblgroupfilter.ParentId = Convert.ToInt32(dr["ParentId"].ToString());
				}

			}
			catch (Exception ex)
			{
				return null;
			}
			return tblgroupfilter;
		}
		public DataTable SelecttblGroupFiltersDynamic(string Where, string Order)
		{
			DataSet ds = new DataSet();
			DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
			SqlParameter[] parameters = new SqlParameter[]{
																new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500),
																new SqlParameter("@OrderByExpression", SqlDbType.NVarChar,250),
															};
			parameters[0].Value = Where;
			parameters[1].Value = Order;
			try
			{
				ds = dataProvider.ExecuteDataSet("sp_SelecttblGroupFiltersDynamic", CommandType.StoredProcedure, parameters);
			}
			catch (Exception ex)
			{
				return null;
			}
			return ds.Tables["Table"];
		}

        public DataTable SelecttblGroupFiltersPageDynamic(int StartIndex, int MaxRecords, string Where, string Order) 
        {
            DataSet ds = new DataSet();
            DataProviderBaseClass dataProvider = DataProvider.GetDataProvider(EnumDataProviderType.SqlClient, DataHelper.GetConnectionString());
            SqlParameter[] parameters = new SqlParameter[]{
                                                                new SqlParameter("@Startindex", SqlDbType.Int),
																new SqlParameter("@Maxrecords", SqlDbType.Int),
																new SqlParameter("@WhereCondition", SqlDbType.NVarChar,500),
																new SqlParameter("@OrderByExpression", SqlDbType.NVarChar,250),
															};
            parameters[0].Value = StartIndex;
            parameters[1].Value = MaxRecords;
            parameters[2].Value = Where;
            parameters[3].Value = Order;
            try
            {
                ds = dataProvider.ExecuteDataSet("sp_SelecttblGroupFiltersPageDynamic", CommandType.StoredProcedure, parameters);
            }
            catch (Exception ex)
            {
                return null;
            }
            return ds.Tables["Table"];
        }

        //Cuong Code
        public static DataTable GetAllParentByCategoryId(int categryid)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tblGroupFilter_GetAllParentByCategory", dbConn);
            dbCmd.Parameters.AddWithValue("@CategoryId", categryid);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataTable GetAllByParentId(int parentid,int categoryid,int top)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tblGroupFilter_GetAllByParentId", dbConn);
            dbCmd.Parameters.AddWithValue("@ParentId", parentid);
            dbCmd.Parameters.AddWithValue("@CategoryId", categoryid);
            dbCmd.Parameters.AddWithValue("@Top", top);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataTable GetAllByCategory(int category)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tblGroupFilter_GetAllByCategoryId", dbConn);
            dbCmd.Parameters.AddWithValue("@CategoryId", category);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataTable GetAllChillByCateParentAndCategory(int category,int cateparent)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("tblGroupFilter_GetAllChillByCateParentAndCategory", dbConn);
            dbCmd.Parameters.AddWithValue("@CategoryId", category);
            dbCmd.Parameters.AddWithValue("@ParentCategory", cateparent);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }



        public bool IsExistedFilterInGroupFilter(int groupFilterId)
        {
            SqlConnection dbConn = new SqlConnection(DataHelper.GetConnectionString());
            try
            {
               
                dbConn.Open();
                SqlCommand dbCmd = new SqlCommand("sp_IsExistedFilterInGroupFilter", dbConn);
                dbCmd.Parameters.AddWithValue("@ID", groupFilterId);
                dbCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
                dbCmd.Parameters[1].Direction = ParameterDirection.ReturnValue;
                dbCmd.CommandType = CommandType.StoredProcedure;

                int kq = dbCmd.ExecuteNonQuery();
                int totalrow = int.Parse(dbCmd.Parameters["@RETURN_VALUE"].Value.ToString());
                if (totalrow > 0)
                    return true;

            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                dbConn.Close();
            }
            return false;
          
        }
    }
}
